﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_leave_cancel')
          and type in ('P','PC'))
   drop procedure bd_proc_leave_cancel
go

CREATE procedure bd_proc_leave_cancel (@orderId varchar(20),@operator varchar(20),@return_remark varchar(max)) as
BEGIN
	DECLARE @status int --单子的审批状态（3：审批通过，4：自动审批）
	DECLARE @BeginDate DATE --单子开始日期
	DECLARE @EndDate DATE --单子结束日期
	DECLARE @users VARCHAR(MAX) = '' --申请的人Id字符串（xxx,xxx,xxx,xxx）
---取消请假存储过程，已审批（通过）和未审批的请假单都能取消。
	begin try
		begin tran

			create table #resetUser(
				user_serial bigint null,
				leave_date date null
			)


			--1收集参数
			select @status = status,
							@BeginDate = start_date ,
							@EndDate = end_date
			from bd_leave_order
			where order_id = @orderId;


			--1更新单子状态为1 is_del（0:正常 ，1：取消）并记录取消信息
			update bd_leave_order
				set is_del = 1,
					remove_user = @operator,
					return_remark = @return_remark,
					remove_time = getdate()
				where order_id = @orderId;

			insert into #resetUser
			select user_serial,kssj from lr_kqingj  where order_id = @orderId and whether_validity = 1;

			--2处理请假类型为放班，处理
			--更新kt_paiba 中的排班状态符号
			DECLARE @leave_bh varchar(20)
			select @leave_bh = leave_id from bd_leave_order where order_id = @orderId
			if @leave_bh = '10003'
				BEGIN
					declare @sql varchar(max) = '' --动态sql
					select @sql += concat('update kt_paiba set d',datepart(dd,leave_date),' =
					'''+'[A]'+'''+ SUBSTRING(d',datepart(dd,leave_date),',4,len(d',datepart(dd,leave_date),')),
					gly_no = ''',@operator,''' where user_serial = ''',user_serial,''' and rq = ''',
					convert(varchar(7),leave_date,120),'''; ')
					from #resetUser

					-- 执行更新排班表
					exec(@sql);
					--更新排班明细表
					update d
						set d.shift_name = '[A]'+SUBSTRING(d.shift_name, 4, len(d.shift_name)),
								d.shift_is_rest = 1
						from bd_kq_schedule_detail d
						INNER JOIN  #resetUser t on d.user_serial = t.user_serial
							and convert(varchar(10),t.leave_date,120) = convert(varchar(10),d.shift_date,120)



				END

			--3记录重新计算
			--遍历调用
--			DECLARE @user_serial bigint
--			DECLARE @ishaveedu int
--
--			DECLARE userOfReset CURSOR LOCAL FOR
--			select x.user_serial,x.ishaveedu from (
--				select l.user_serial,ISNULL(k.ishaveedu, 0) as ishaveedu
--				from lr_kqingj l
--				INNER JOIN kt_qingj k on k.bh = l.bh
--				where l.order_id = @orderId and l.whether_validity = 1
--		) x GROUP BY x.user_serial,x.ishaveedu
--
--			OPEN userOfReset FETCH NEXT FROM userOfReset INTO @user_serial,@ishaveedu
--			WHILE @@fetch_status = 0
--				BEGIN
--						--重新计算
--						exec bd_proc_reset_data @BeginDate,@ishaveedu,@user_serial,@operator
--
--					FETCH NEXT FROM userOfReset INTO @user_serial,@ishaveedu
--				END
        EXEC bd_proc_vacation_cancel_order @orderId = @orderId
                                          ,@userIds = ''
                                          ,@operateId = @operator


--			--4重新计算考勤
--			if (@status=3 or @status=4)
--				BEGIN
--					SELECT
--							@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
--						FROM (
--								select DISTINCT user_serial from lr_kqingj where order_id = @orderId
--						) u
--
--					SET @users = SUBSTRING(@users,0, LEN(@users))
--					EXEC bd_proc_timecard_extend @BeginDate, @EndDate, @users,7
--				END
		commit tran
	end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
				ROLLBACK TRANSACTION;
				RAISERROR (@errStr, 16, 1);
			END;
	end catch
END
GO